import datetime
import pandas as pd
from quant_researcher.quant.project_tool.time_tool import datetime_to_timestamp, timestamp_to_str, timestamp_to_datetime
from urllib.parse import urljoin
import requests
import json
import os


Binance_spot_base_url = "https://api.binance.com"
Binance_um_futures_base_url = 'https://fapi.binance.com'
Binance_cm_futures_base_url = 'https://dapi.binance.com'
Binance_options_base_url = 'https://vapi.binance.com'

Binance2FREQUENCY_DICT = {
    "1m": '1min',
    "5m": '5min',
    "15m": '15min',
    "30m": '30min',
    "1h": '60min',
    "8h": '8hour',
    "1d": 'day',
}
"""
binance 只允许一次获取 500bar，时间请求超过范围则只返回最新500条
"""
FREQUENCY_SHIFTING = {
    "1m": 30000,
    "5m": 150000,
    "15m": 450000,
    "30m": 900000,
    "1h": 1800000,
    "8h": 1800000,
    "1d": 14400000,
}


def fetch_binance_kline(symbol, start_time, end_time, frequency):
    """
    Get the latest symbol‘s candlestick data
    时间倒序切片获取算法，是各大交易所获取1min数据的神器，因为大部分交易所直接请求跨月跨年的1min分钟数据
    会直接返回空值，只有将 start_epoch，end_epoch 切片细分到 200/300 bar 以内，才能正确返回 kline，
    火币和binance，OKEx 均为如此，直接用跨年时间去直接请求上万bar 的 kline 数据永远只返回最近200条数据。
    """

    def format_binance_klines_data_fields(datas, symbol, frequency):
        """
        # 归一化数据字段，转换填充必须字段，删除多余字段
        参数名 	类型 	描述
        time 	String 	开始时间
        open 	String 	开盘价格
        high 	String 	最高价格
        low 	String 	最低价格
        close 	String 	收盘价格
        volume 	String 	交易量
        """

        column_names = ['start_time', 'open', 'high', 'low', 'close', 'volume', 'close_time', 'quote_asset_volume',
                        'num_trades', 'buy_base_asset_volume', 'buy_quote_asset_volume', 'Ignore']

        frame = pd.DataFrame(datas, columns=column_names)
        frame = frame.astype(float)
        frame['symbol'] = 'BINANCE.{}'.format(symbol)
        # UTC时间戳转换为UTC日期时间
        frame['start_time'] = frame.apply(lambda x: int(x['start_time'] / 1000), axis=1)
        frame['datetime'] = frame['start_time'].apply(timestamp_to_datetime, tz_str='+0000')
        frame['date'] = frame['datetime'].dt.strftime('%Y-%m-%d')
        frame['datetime'] = frame['datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')
        frame.rename({'num_trades': 'trade', 'start_time': 'time_stamp', 'buy_quote_asset_volume': 'amount'}, axis=1, inplace=True)
        if (frequency not in ['1day', Binance2FREQUENCY_DICT['1d'], '1d']):
            frame['type'] = Binance2FREQUENCY_DICT[frequency]
        frame.drop(['close_time', 'quote_asset_volume', 'buy_base_asset_volume', 'Ignore'], axis=1, inplace=True)
        return frame

    datas = list()
    reqParams = {}
    reqParams['from'] = end_time - FREQUENCY_SHIFTING[frequency]
    if reqParams['from'] < start_time:
        reqParams['from'] = start_time
    reqParams['to'] = end_time

    while (reqParams['to'] > start_time):
        if ((reqParams['from'] > datetime_to_timestamp())) or ((reqParams['from'] > reqParams['to'])):
            # 出现“未来”时间，一般是默认时区设置，或者时间窗口滚动前移错误造成的
            print(
                'A unexpected \'Future\' timestamp got, Please check self.missing_data_list_func param \'tzlocalize\' '
                'set. More info: {:s}@{:s} at {:s} but current time is {}'
                    .format(
                    symbol,
                    frequency,
                    timestamp_to_str(reqParams['from']),
                    timestamp_to_str(datetime_to_timestamp())
                )
            )
            # 跳到下一个时间段
            reqParams['to'] = int(reqParams['from'] - 1)
            reqParams['from'] = int(reqParams['from'] - FREQUENCY_SHIFTING[frequency])
            if reqParams['from'] < start_time:
                reqParams['from'] = start_time
            continue

        url = urljoin(Binance_spot_base_url, "/api/v1/klines")
        params = {
            "symbol": symbol,
            "interval": frequency,
            "startTime": int(reqParams['from'] * 1000),
            "endTime": int(reqParams['to'] * 1000)
        }
        print(f"开始获取{timestamp_to_str(reqParams['from'])} - {timestamp_to_str(reqParams['to'])}的数据")
        req = requests.get(url=url, params=params)
        klines = json.loads(req.content)

        if (klines is None) or (len(klines) == 0) or ('error' in klines):
            # 出错放弃
            break

        reqParams['to'] = int(reqParams['from'] - 1)
        reqParams['from'] = int(reqParams['from'] - FREQUENCY_SHIFTING[frequency])
        if reqParams['from'] < start_time:
            reqParams['from'] = start_time

        if (klines is None) or ((len(datas) > 0) and (klines[-1][0] == datas[-1][0])):
            # 没有更多数据
            break

        datas.extend(klines)


    if len(datas) == 0:
        return None

    # 归一化数据字段，转换填充必须字段，删除多余字段
    frame = format_binance_klines_data_fields(datas, symbol, frequency)
    frame.set_index('date', inplace=True)
    frame.sort_index(inplace=True)

    return frame


if __name__ == '__main__':
    start = datetime.datetime(2025, 8, 1, 0, 0, 0, tzinfo=datetime.timezone.utc).timestamp()
    end = datetime.datetime(2025, 8, 29, 0, 0, 0, tzinfo=datetime.timezone.utc).timestamp()
    data = fetch_binance_kline("BTCUSDT", start, end, '1h')
    data.sort_values(by='datetime', inplace=True)
    file_name = os.path.join("G:/", f'binance_ohlcv')
    data.to_excel(f'{file_name}.xlsx')